<?php
App::import('Vendor', 'linkbot');
App::import('Vendor', 'email_message');
App::import('Vendor', 'image');

class Page extends AppModel{
	var $name              = 'Page';
	var $useTable          = 'users';
	var $userInfoTable     = 'users_information';
	var $markerTable       = 'markers';
	var $userPropertyTable = 'user_properties';
	var $newsTable         = 'news';
	var $destructionTable  = 'destruction';
	var $gameTable         = 'game_info';
	var $gameTimeTable         = 'game_times';
	var $gameLocatonsTable = 'game_locations';
	var $regionsTable      = 'regions';
	
	
	function getSwarmRegionInfo($game_id){
		$query = "SELECT r.`region_id`,
				 	p.`property_name`, p.`user_id`,
				 	u.`first_name`, u.`last_name`,
				 	m.`building_type`
				FROM `{$this->regionsTable}` as r, `{$this->userPropertyTable}` as p,
				 	`{$this->userInfoTable}` as u, `{$this->markerTable}` as m
				WHERE r.game_id = {$game_id} AND
				 	r.`region_id` = m.`region_id` AND 
				 	p.`marker_id` = m.`marker_id` AND
				 	p.`user_id`   = u.`user_id`";	
		$result = $this->query($query);
		$swarm_info = array();
		foreach($result as $res){
			$temp = array_merge($res["p"],$res["m"],$res["r"],$res["u"]);
			if($temp["region_id"]%100 != 0){
				$nr        = (int)($temp["region_id"]/100);
				if($nr !=0 ){ 	
					$region_id = ($temp["region_id"]%($nr*100));	
				}else{
					$region_id = $temp["region_id"];	
				}	
			}else{
				$region_id = 100;	
			}	
			if(!array_key_exists ($region_id,$swarm_info)){
				$swarm_info[$region_id] = array();
			}
			$swarm_info[$region_id][] = $temp;
		}
		for($i=1;$i<=100;$i++){
			if(!array_key_exists ($i,$swarm_info)) {
				$swarm_info[$i] = array();
			}	
		}
		return $swarm_info;
	}
	
	
	function getGameInfo($game_id){
		$result = $this->query("SELECT l.*, g.`game_id`, g.`game_name` 
					 FROM `{$this->gameLocatonsTable}` as l, 
					 		`{$this->gameTable}` as g 
					 WHERE g.`game_id` = {$game_id} 
					 	AND g.`game_location_id` = l.`game_location_id`");
		$game = array();
		foreach($result as $res){
			$game = array_merge($res['l'],$res['g']);
		}
		return $game;
	}
	
	function userProfiles($game_id) {
		$query1 = "SELECT u.`user_id`, u.`first_name`,u.`last_name`,u.`img_url`				
				FROM `{$this->userInfoTable}` AS `u`, `{$this->gameTable}` as `g` 
				WHERE g.`user_id` = u.`user_id` AND g.`game_id` = {$game_id}";
		$result = $this->query($query1);
		$user_info = array();
		foreach ($result as $res) {
			//get nr of properties_______________________________________
			$query2 = "SELECT COUNT(p.`property_id`) AS `no_props`				
				FROM `{$this->userPropertyTable}` AS `p`,
						`{$this->markerTable}` AS `m`
				WHERE p.`user_id` = {$res['u']['user_id']} AND 
					p.`marker_id` = m.`marker_id` AND
					m.`valid` = 1
				GROUP BY p.`user_id`";
			$result2 = $this->query($query2);
			$temp = array();
			foreach($result2 as $res2) {
				$temp['no_props'] = $res2[0]['no_props'];
			}
			//get no of received attacks__________________________________			
			$query3 = "SELECT COUNT(a.`news_id`) AS `no_received_attacks`
					FROM `{$this->newsTable}` AS `a`				 
					WHERE a.`marker_id` IN (SELECT m.`marker_id` 
									FROM `{$this->markerTable}` AS `m`,
									 	`{$this->userPropertyTable}` AS `p`
									WHERE m.`marker_id`= p.`marker_id` 
										AND p.`user_id` = {$res['u']['user_id']})";
			$result3 = $this->query($query3);
			foreach($result3 as $res3) {
				$temp['no_received_attacks'] = $res3[0]['no_received_attacks'];
			}			
			//get no of sent attacks__________________________________________
			$query4 = "SELECT COUNT(n.`news_id`) AS `no_sent_attacks`				
					FROM `{$this->newsTable}` AS `n`			 
					WHERE n.`author_id` = {$res['u']['user_id']}";
			$result4 = $this->query($query4);
			foreach($result4 as $res4) {
				$temp['no_sent_attacks'] = $res4[0]['no_sent_attacks'];
			}
			//get favorite attacks____________________________________________
			$query5 = "SELECT d.`destruction_name`, 
				COUNT( n.`destruction_id` ) AS `nr` 
				FROM `{$this->destructionTable}` AS d, `{$this->newsTable}` AS n
				WHERE d.`destruction_id` = n.`destruction_id`
					AND n.`author_id` = {$res['u']['user_id']}
				GROUP BY n.`destruction_id`
				ORDER BY nr DESC
				LIMIT 0 , 1";
			$result5 = $this->query($query5);
			foreach($result5 as $res5) {
				$temp['fav_attack'] = $res5['d']['destruction_name'];
			}
			//get favorite building___________________________________________
			$query6 = "SELECT m.`building_type`,
				COUNT( m.`building_type` ) AS `nr` 
				FROM `{$this->userPropertyTable}` AS p, `{$this->markerTable}` AS m
				WHERE p.`user_id` = {$res['u']['user_id']} AND
					p.`marker_id`= m.`marker_id` AND
					m.`valid` = 1
				GROUP BY m.`building_type`
				ORDER BY nr DESC
				LIMIT 0 , 1";
			$result6 = $this->query($query6);
			foreach($result6 as $res6) {
				$temp['fav_building'] = $res6['m']['building_type'];
			}
			// calculate net worth___________________________________________
			$query7 = "SELECT SUM(m.`revenue`) AS `total_revenue`		
				FROM `{$this->markerTable}` AS `m`,
					`{$this->userPropertyTable}` AS `p`			 
				WHERE m.`marker_id` = p.`marker_id` AND
					p.`user_id`={$res['u']['user_id']} AND
					m.`valid` = 1
				GROUP BY p.`user_id`";
			$result7 = $this->query($query7);
			foreach($result7 as $res7) {
				$temp['total_revenue'] = $res7[0]['total_revenue'];
			}
			// append temp to userarray______________________________________
			$user_info[$res['u']['user_id']] = array_merge($res['u'], $temp);
		}
		return $user_info;
	}
	
	
	function userInformation($user_id) {
		$query1 = "SELECT u.`user_id`, u.`first_name`,u.`last_name`,u.`img_url`,u.`money`				
				FROM `{$this->userInfoTable}` AS `u` 
				WHERE u.`user_id`={$user_id}";
		$result = $this->query($query1);
		$user_info = array();
		foreach ($result as $res) {
			$user_info = $res['u'];
			//get nr of properties_______________________________________
			$query2 = "SELECT COUNT(p.`property_id`) AS `no_props`				
			FROM `{$this->userPropertyTable}` AS `p`,
					`{$this->markerTable}` AS `m`
			WHERE p.`user_id`={$res['u']['user_id']} AND
				m.`marker_id` = p.`marker_id` AND
				m.`valid` = 1
			GROUP BY p.`user_id`";
			$result2 = $this->query($query2);
			foreach($result2 as $res2) {
				$user_info['no_props'] = $res2[0]['no_props'];
			}
			//get total revenue__________________________________________
			$query3 = "SELECT SUM(m.`revenue`) AS `total_revenue`		
			FROM `{$this->markerTable}` AS `m`,
				`{$this->userPropertyTable}` AS `p`			 
			WHERE m.`marker_id` = p.`marker_id` AND
				p.`user_id`={$res['u']['user_id']} AND
				m.`valid`=1 
			GROUP BY p.`user_id`";
			$result3 = $this->query($query3);
			foreach($result3 as $res3) {
				$user_info['total_revenue'] = $res3[0]['total_revenue'];
			}
			//get no of received attacks__________________________________			
			$query4 = "SELECT COUNT(a.`news_id`) AS `no_received_attacks`
					FROM `{$this->newsTable}` AS `a`				 
					WHERE a.`marker_id` IN (SELECT m.`marker_id` 
									FROM `{$this->markerTable}` AS `m`,
									 	`{$this->userPropertyTable}` AS `p`
									WHERE m.`marker_id`= p.`marker_id` 
										AND p.`user_id` = {$res['u']['user_id']})";
			$result4 = $this->query($query4);
			foreach($result4 as $res4) {
				$user_info['no_received_attacks'] = $res4[0]['no_received_attacks'];
			}			
			//get no of sent attacks__________________________________________
			$query5 = "SELECT COUNT(n.`news_id`) AS `no_sent_attacks`				
					FROM `{$this->newsTable}` AS `n`			 
					WHERE n.`author_id` = {$res['u']['user_id']}";
			$result5 = $this->query($query5);
			foreach($result5 as $res5) {
				$user_info['no_sent_attacks'] = $res5[0]['no_sent_attacks'];
			}

		}
		return $user_info;
	}
	
	
	function affordable($property_value, $user_id){
		$query = "SELECT `money`
				 FROM `{$this->userInfoTable}` 
				 WHERE `user_id`={$user_id}";
		$result = $this->query($query);
		$money = array();
		foreach($result as $res){
			$money = $res[$this->userInfoTable]['money'];
		}
		if($money<=$property_value) {
			return " - You do not have enough money to take this action.";
		}else {
			return "";
		}
	}
	

	function isPropertyThere($lat,$long,$user_id,$game_id){
		$query = "SELECT * 
				 FROM `{$this->markerTable}` AS `m`,
				 	`{$this->userPropertyTable}` AS `p`,
				 	`{$this->gameTable}` AS `g`
				 WHERE m.`pos_lat`={$lat} AND m.`pos_long`={$long} 
				 	AND m.`marker_id`=p.`marker_id` 
				 	AND p.`user_id`={$user_id} 
				 	AND m.`valid`=1 
				 	AND g.`user_id` = p.`user_id` 
				 	AND g.`game_id` = {$game_id}";
		$result = $this->query($query);
		if(count($result)>0){
			return "You have already bought this property!";
		}
		return "";
	}
	
	
	function addProperty($params, $lat, $long, $bcond, $user_id, $region, $revenue) {
		//echo "addProperty: ".$revenue;
		//create the marker in the markers table________________________________
		$query = "INSERT INTO `{$this->markerTable}` (`building_type`, `revenue`,
				`building_condition`, `pos_lat`, `pos_long`, `value`, `region_id`)
				VALUES ('{$params['building_type']}', {$revenue},'{$bcond}', 
					{$lat},{$long},{$params['bprice']},{$region['region_id']})";
		$result = $this->query($query);
		
		$result = $this->query("SELECT LAST_INSERT_ID() AS `id`");
		foreach($result as $res){
			$prop_id = $res[0]['id'];
		}
		//if there is an image for the property_________________________________
		if(!empty($params['building_img']['name']) && !empty($params['building_img']['size'])) {
			$urlimg = "img/property/property_{$user_id}.{$prop_id}.jpg";		
			try{
	 			move_uploaded_file($params['building_img']['tmp_name'] ,'img/property/'.$params['building_img']['name']);
	 		}catch(Execption $e){}
	 			$img = new Wizart_Image('img/property/'.$params['building_img']['name']);
				$img->cropToSize(120,60)->store($urlimg);	
				if(file_exists('img/property/'.$params['building_img']['name'])) 
					unlink('img/property/'.$params['building_img']['name']);
		} else {
			$urlimg = "img/property/house.jpg"; 
		}		
		//insert the property details in the DB_________________________________
		$query = "INSERT INTO `{$this->userPropertyTable}` (`user_id`, `marker_id`, 
			`date_buy`, `price_buy`, `img_url`, `forsale`,`property_name`)
			VALUES ({$user_id}, {$prop_id}, SYSDATE(),{$params['bprice']}, 
			'{$urlimg}',0, '".$this->escape($params['property_name'])."')";
		$result = $this->query($query);
		//update the money info_________________________________________________
		$query = "UPDATE `{$this->userInfoTable}` 
				SET `money` = (`money` - {$params['bprice']}) 
				WHERE `user_id` = {$user_id}";
		$result = $this->query($query);
	}
	
	function upgradeBuilding($params,$marker_id, $user_id, $upgrade_val, $upgrade_revenue) {
		//update the building info_________________________________________________
		$query = "UPDATE `{$this->markerTable}` 
				SET `building_condition` = '{$params['building_cond']}',
					`building_type` = '{$params['building_type']}',
					`revenue` = {$upgrade_revenue}
				WHERE `marker_id` = {$marker_id}";
		$result = $this->query($query);		
		
		if(isset($params['property_name']) && !empty($params['property_name'])) {
			$query = "UPDATE `{$this->userPropertyTable}` 
				SET `property_name` = '{$params['property_name']}' 
				WHERE `marker_id` = {$marker_id} AND `user_id` = {$user_id}";
			$result = $this->query($query);		
		}
		
		$query = "SELECT * 
				FROM `{$this->userPropertyTable}` 
				WHERE `marker_id` = {$marker_id} and `user_id` = {$user_id}";
		$result = $this->query($query);		
		foreach($result as $res) {
			$prop_id = $res[$this->userPropertyTable]['property_id'];	
		}
		
		//if there is an image for the property_________________________________
		if(!empty($params['building_img']['name']) && !empty($params['building_img']['size'])) {
			$urlimg = "img/property/property_{$user_id}.{$prop_id}.jpg";		
			try{
	 			move_uploaded_file($params['building_img']['tmp_name'] ,'img/property/'.$params['building_img']['name']);
	 		}catch(Execption $e){}
	 			$img = new Wizart_Image('img/property/'.$params['building_img']['name']);
				$img->cropToSize(120,60)->store($urlimg);	
				if(file_exists('img/property/'.$params['building_img']['name'])) 
					unlink('img/property/'.$params['building_img']['name']);
			$query = "UPDATE `{$this->userPropertyTable}` 
					SET `img_url` = '{$urlimg}' 
					WHERE `marker_id` = {$marker_id} AND `user_id`={$user_id}";
			$result = $this->query($query);		
		}	
		
		//update the money info_________________________________________________
		$query = "UPDATE `{$this->userInfoTable}` 
				SET `money` = (`money` - {$upgrade_val}) 
				WHERE `user_id` = {$user_id}";
		$result = $this->query($query);		
	}
	
	function getNews($user_id, $game_id) {
		$query = "SELECT n.`marker_id`,n.`date_start`,n.`date_end`,d.`destruction_name`,
				 d.`effect_description`, d.`destruction_impact`,d.`destruction_img`,
				 n.`author_id`, a.`first_name`,a.`last_name`, a.`img_url` AS `author_img`, 
				 m.`building_type`, m.`building_condition`, m.`valid` AS `marker_valid`,
				 p.`property_name`, p.`img_url` AS `property_img`,n.`valid`
			FROM `{$this->newsTable}` AS `n`, `{$this->markerTable}` as `m`, 
				`{$this->userInfoTable}` AS `a`, `{$this->destructionTable}` as `d`,
				`{$this->userPropertyTable}` AS `p` 		 
			WHERE n.`marker_id` = m.`marker_id` AND 
				  m.`marker_id` = p.`marker_id` AND 
				  n.`author_id` = a.`user_id` AND 
				  n.`destruction_id` = d.`destruction_id` AND 
				n.`marker_id` IN (SELECT u.`marker_id` 
							FROM `{$this->userPropertyTable}` as u,
								`{$this->gameTable}` as `g` 
							WHERE u.`user_id` = {$user_id} 
								AND g.`game_id` = {$game_id} 
								AND g.`user_id` = u.`user_id`) 
				AND n.`date_end` >= SYSDATE() 
				AND n.`valid`=1 
			ORDER BY n.`news_id` DESC";
		$result = $this->query($query);
		$news = array();
		if(count($result)>0) {
			foreach($result as $res) {
				$news[] = array_merge($res['n'],$res['m'],$res['a'],$res['d'],$res['p']);
			}	
		}
		return $news;
	}

	function buyDestruction($params,$marker_id,$user_id){
		$end_date = date('Y-m-d', mktime(0,0,0,date('m'),date('d')+($params['destruction_duration']),date('Y'))); 
		$query = "INSERT INTO `{$this->newsTable}`(`marker_id`,`date_start`,`date_end`,`destruction_id`,`author_id`) 
			VALUES ({$marker_id},SYSDATE(),'{$end_date}',{$params['destruction_id']},{$user_id})";
		$result = $this->query($query);
		
		//select old building condition
		$result = $this->query("SELECT `building_condition` 
								FROM `{$this->markerTable}` WHERE `marker_id` = {$marker_id}");
		$new_build_cond = 0;
		foreach($result as $res){
			$build_cond = $res[$this->markerTable]['building_condition'];
			$new_build_cond = max((int)$build_cond-$params['destruction_impact'],0);
		}
		//update the building condition after the impact_______________________
		$query = "UPDATE `{$this->markerTable}` 
				SET `building_condition` = '{$new_build_cond}'".
				($new_build_cond<10?", `valid`=0 ":""). 
				"WHERE `marker_id` = {$marker_id}";
		$result = $this->query($query);		
	
		//update the money_____________________________________________________
		$query = "UPDATE `{$this->userInfoTable}` 
				SET `money` = (`money` - {$params['destruction_price']}) 
				WHERE `user_id` = {$user_id}";
		$result = $this->query($query);		
	}
	
	function getGameStatistics($game_id) {
		$statistics = array();

		$gettime = "SELECT g.`game_time`, g.`game_time_max`, g.`total_population` 
			FROM {$this->gameTimeTable} AS `g`
			WHERE g.`game_id` = {$game_id}";
		
		$result = $this->query($gettime);
		$statistics['game_time'] = $result[0]['g']['game_time']; 
		$statistics['game_time_max'] = $result[0]['g']['game_time_max']; 
		$statistics['total_population'] = $result[0]['g']['total_population']; 
		
		$getcityname = "SELECT DISTINCT l.`city_name`
					FROM `{$this->gameLocatonsTable}` AS `l`,
					{$this->gameTable} AS `g`
					WHERE l.`game_location_id` = g.`game_location_id`
					AND g.`game_id` = {$game_id}";
		$result = $this->query($getcityname);
		$statistics['city_name'] = $result[0]['l']['city_name']; 
		
		$getcityname = "SELECT DISTINCT g.`game_name`
					FROM {$this->gameTable} AS `g`
					WHERE g.`game_id` = {$game_id}";
		$result = $this->query($getcityname);
		$statistics['game_name'] = $result[0]['g']['game_name']; 
		
		return $statistics;
	}
}